﻿CREATE PROCEDURE [dbo].[sp_InsertRawEventFromQueue]

AS
SET NOCOUNT ON;

DECLARE
	@message_body XML,
	@message_type_name NVARCHAR(256)

WHILE (1=1)
	BEGIN
		BEGIN TRANSACTION;

			--Get message from Queue
			--If no messages for 2 seconds, drop out
			WAITFOR (
				RECEIVE TOP(1)
					@message_type_name = message_type_name,
					@message_body = message_body
				FROM
					RawEventQueue
					), TIMEOUT 2000;

			--if nothing was received, roll back and break out of while loop
			IF (@@ROWCOUNT = 0)
				BEGIN
					ROLLBACK TRANSACTION;
					BREAK;
				END;

			--Make sure this isn't the EndDialog message
			--In the future we may want to specifically create a message type in ServiceBroker as opposed to using default
			IF(@message_type_name <> 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
				BEGIN
					INSERT INTO [AMI].[dbo].[RawEvents] WITH(ROWLOCK)
						(
						[MeterNumber]
					   ,[MeterVendorID]
					   ,[MeterHeadEndID]
					   ,[TimeStamp]
					   ,[EventType]
					   ,[Reading]
					   ,[Temp]
					   )
				 VALUES
					   (
					   CAST(@message_body.query('/MeterEvent/MeterInfo/Number/text()') AS NVARCHAR(50)), --MeterNumber
					   CAST(@message_body.query('/MeterEvent/MeterInfo/VendorID/text()') AS NVARCHAR(50)), --MeterVendorID
					   CAST(@message_body.query('/MeterEvent/MeterInfo/HeadendID/text()') AS NVARCHAR(50)), --MeterHeadEndID
					   CONVERT(datetime,CAST(@message_body.query('/MeterEvent/TimeStamp/text()') AS NVARCHAR(50))),     --TimeStamp
					   CAST(@message_body.query('/MeterEvent/Type/text()') AS NVARCHAR(50)), --EventType
					   CONVERT(float,CAST(@message_body.query('/MeterEvent/Data/CurrentReading/text()') AS NVARCHAR(50))),        --Reading
					   CONVERT(float,CAST(@message_body.query('/MeterEvent/Data/Temp/text()') AS NVARCHAR(50)))       --Temp
					   )
				END;

		COMMIT TRANSACTION;

	END;
